﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Consulta.Core;
using Softpro.AccesoDatosDB2;

namespace Consulta.Manager
{
    public class MTesoreria
    {
        //Terceros
        public DataTable ConsultarLista(string pFiltro, string pClausulaOrderBy)
        {

            try
            {
                DataTable tabla = new DataTable();
                accesoDatos p = new accesoDatos();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    string tSql;
                    tSql = "SELECT FNOMBT As Nombre," +
                        " FNITAL AS Nit," +
                        " FDIREC AS Direccion," +
                        " FTELEF AS Telefono," +
                        " FESTAD AS Estado, " +
                        " COALESCE(T2.ACRNIT, 0) As NitCXP " +
                        " FROM CNBEDP.CNNITEDP T1 " +
                        " LEFT OUTER JOIN CPXDATOS.ACRBAS T2 ON (T1.FNITAL = T2.ACRNIT) ";
                    try
                    {
                        long tNit = Convert.ToInt64(pFiltro);
                        tSql += " WHERE FNITAL = " + tNit;
                    }
                    catch (FormatException)
                    {
                        //Si no es numerico
                        tSql += " WHERE FNOMBT Like '%" + pFiltro.ToUpper() + "%' ";

                        if (pClausulaOrderBy.Length > 0)
                        {
                            tSql += " ORDER BY " + pClausulaOrderBy;
                        }
                        else
                        {
                            tSql += " ORDER BY Nombre";
                        }
                    }
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        //Vencimientos
        public DataTable ConsultarPagos(string pFiltro, string pDesde, string pHasta, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    DateTime tDesde = Convert.ToDateTime(pDesde);
                    DateTime tHasta = Convert.ToDateTime(pHasta);
                    accesoDatos p = new accesoDatos();
                    tSql = crearConsultaPago(pFiltro, tDesde, tHasta, pClausulaOrderBy);
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        public DataTable ConsultarVencimientos(string pFiltro, string pDesde, string pHasta, string pClausulaOrderBy)
        {
            try
            {
                string tSql;
                DataTable tabla = new DataTable();
                if (pFiltro != null && pFiltro.Length > 0)
                {
                    DateTime tDesde = Convert.ToDateTime(pDesde);
                    DateTime tHasta = Convert.ToDateTime(pHasta);
                    accesoDatos p = new accesoDatos();
                    tSql = crearConsultaVencimiento(pFiltro, tDesde, tHasta, pClausulaOrderBy);
                    tabla = p.leerDatosSQL(tSql);
                }
                return tabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
        }
        private string crearConsultaPago(string pFiltro, DateTime tDesde, DateTime tHasta, string pClausulaOrderBy)
        {
            //" (SUBSTR(T1.EGRFCH,1,4)|| '-'|| SUBSTR(T1.EGRFCH,5,2) || '-' ||SUBSTR(T1.EGRFCH,7,2)) AS Fecha," +
            string tCampos = " T1.EGRDSN AS Acreedor," +
                        " T1.EGRNIT AS Nit," +
                        " T1.EGRCMP As Comprobante," +
                        " DATE(SUBSTR(T1.EGRFCH,1,4) || '-'|| SUBSTR(T1.EGRFCH,5,2) || '-' || SUBSTR(T1.EGRFCH,7,2)) AS Fecha," +
                        " T1.EGRTIPPAG AS Tipo," +
                        " T1.EGRNDC AS Documento," +
                        " CASE (T1.EGRMON) " +
                        "   WHEN 'US' then 'Dolares' " +
                        "   ELSE 'Colones' " +
                        " END AS Moneda," +
                        " T1.EGRVNT AS Valor," +
                        " T1.EGRCon AS Conc," +
                        " T2.CTCDSC As Cuenta ";
            string tCondicion = " WHERE " +
                        " T1.EGRCTC = T2.CTCCTC " +
                        " AND (T1.EGRSTS = '6' OR T1.EGRSTS = '7') " +
                        " AND T1.EGRFCH between '" + tDesde.ToString("yyyyMMdd") + "' And '" + tHasta.ToString("yyyyMMdd") + "' ";
            try
            {
                tCondicion += " AND T1.EGRNIT = " + Int64.Parse(pFiltro);
            }
            catch (FormatException)
            {
                tCondicion += " AND T1.EGRDSN LIKE '%" + pFiltro.ToUpper() + "%' ";
            }
            string tSql = "SELECT 'BAC' as Cia, " + tCampos +
                        " FROM " +
                        " CPXDTCRP.EGRBAS As T1, " +
                        " CPXDTCRP.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'TER' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTTEP.EGRBAS As T1, " +
                        " CPXDTTEP.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'AGC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTAGC.EGRBAS As T1, " +
                        " CPXDTAGC.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'FTV' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTFTV.EGRBAS As T1, " +
                        " CPXDTFTV.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'INC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTINC.EGRBAS As T1, " +
                        " CPXDTINC.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'BAN' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTBAN.EGRBAS As T1, " +
                        " CPXDTBAN.CTCBAS As T2" +
                        tCondicion +
                    " UNION " +
                        "SELECT 'CAY' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTCAY.EGRBAS As T1, " +
                        " CPXDTCAY.CTCBAS As T2" +
                        tCondicion;
            if (pClausulaOrderBy.Length > 0)
            {
                tSql += " ORDER BY " + pClausulaOrderBy;
            }
            else
            {
                tSql += " ORDER BY Fecha";
            }
            return tSql;
        }
        private string crearConsultaVencimiento(string pFiltro, DateTime tDesde, DateTime tHasta, string pClausulaOrderBy)
        {
            string tCampos = " T2.OBLDSN As Acreedor," +
                        "  T2.OBLNIT As Nit," +
                        "  T2.OBLNDC AS Factura," +
                        " DATE(SUBSTR(T2.OBLFCH,1,4) || '-'|| SUBSTR(T2.OBLFCH,5,2) || '-' || SUBSTR(T2.OBLFCH,7,2)) AS Fecha," +
                        " DATE(SUBSTR(T2.OBLFVC,1,4) || '-'|| SUBSTR(T2.OBLFVC,5,2) || '-' || SUBSTR(T2.OBLFVC,7,2)) AS Vencimiento," +
                        "  SUBSTR(T2.OBLFVC,1,4) As Anno," +
                        "  T3.CBNSEM As Semana," +
                        "  CASE (T2.OBLMON) " +
                        "    WHEN 'US' then 'Dolares' " +
                        "    ELSE 'Colones' " +
                        "  END AS Moneda," +
                        "  T2.OBLVNT As Valor," +
                        "  T2.OBLVAP As Abonos," +
                        "  (T2.OBLVNT - T2.OBLVAP) As Saldo," +
                        "  T2.OblObs As Observacion";
            string tCondicion = " WHERE " +
                        " T3.CBNFDS <= T2.OBLFVC" +
                        " AND T3.CBNFHS >= T2.OBLFVC" +
                        " AND T2.OBLFVC BETWEEN '" + tDesde.ToString("yyyyMMdd") + "' And '" + tHasta.ToString("yyyyMMdd") + "' " +
                        " AND T2.OBLSTS = '2'";
            try
            {
                tCondicion += " AND T2.OBLNIT = " + Int64.Parse(pFiltro);
            }
            catch (FormatException)
            {
                tCondicion += " AND T2.OBLDSN LIKE '%" + pFiltro.ToUpper() + "%' ";
            }
            string tSql = "SELECT 'BAC' as Cia, " + tCampos +
                        " FROM " +
                        " CPXDTCRP.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'TER' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTTEP.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'AGC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTAGC.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'FTV' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTFTV.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'INC' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTINC.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'BAN' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTBAN.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion +
                    " UNION " +
                        "SELECT 'CAY' as Cia," + tCampos +
                        " FROM " +
                        " CPXDTCAY.OBLBAS AS T2, CPXDATOS.CBNBAS AS T3 " +
                        tCondicion;
            if (pClausulaOrderBy.Length > 0)
            {
                tSql += " ORDER BY " + pClausulaOrderBy;
            }
            else
            {
                tSql += " ORDER BY Fecha";
            }
            return tSql;

        }
        //Pagos
        public Egreso consultarID(string pLibDatos, int pNumeComp)
        {
            try
            {
                accesoDatos p = new accesoDatos();
                string tSql = "SELECT T1.EgrFch, " +
                        " T1.EgrDsn," +
                        " T1.EgrNit," +
                        " T1.EgrNdc," +
                        " T1.EgrTipPag," +
                        " T1.EgrCon," +
                        " T1.EgrSts," +
                        " T1.EgrMon," +
                        " T1.EgrVnt," +
                        " T2.CtcDsc " +
                        " FROM " + pLibDatos + ".EGRBAS T1" +
                        " JOIN " + pLibDatos + ".CTCBAS T2 ON " +
                        " (T1.EgrEfn = T2.CtcEfn " +
                        " AND T1.EgrCtc = T2.CtcCtc) " +
                        " WHERE T1.EgrCmp = " + pNumeComp;
                DataTable tTabla = p.leerDatosSQL(tSql);
                Egreso tEgreso = new Egreso();
                //Asignar valores del registro
                DataRow tFila = tTabla.Rows[0];
                tEgreso.EgrCmp = pNumeComp;
                tEgreso.EgrFch = tFila["EgrFch"].ToString();
                tEgreso.EgrDsn = tFila["EgrDsn"].ToString();
                tEgreso.EgrNdc = tFila["EgrNdc"].ToString();
                tEgreso.EgrNit = Convert.ToInt64(tFila["EgrNit"]);
                tEgreso.EgrTipPag = tFila["EgrTipPag"].ToString();
                tEgreso.EgrCon = tFila["EgrCon"].ToString();
                tEgreso.EgrSts = tFila["EgrSts"].ToString();
                tEgreso.EgrCta = tFila["CtcDsc"].ToString();
                tEgreso.EgrMon = tFila["EgrMon"].ToString();
                tEgreso.EgrVnt = Convert.ToDouble(tFila["EgrVnt"]);
                return tEgreso;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public DataTable ConsultarDetallePago(string pLibDatos, int pEgreso, string pConcepto)
        {
            try
            {
                accesoDatos p = new accesoDatos();
                string tSql;
                if (pConcepto == "PGD")
                {
                    //Traer datos del pago directo
                    tSql = "SELECT 'PagoDirecto' as Num1, " +
                        " PgdNum AS Num2," +
                        " PgdFch AS Fecha," +
                        " PgdVlr As Valor," +
                        " PgdVlrP As Valor2," +
                        " PgdMon as Moneda," +
                        " PgdNdc As Documento," +
                        " PgdObs As Observacion," +
                        " PgdTipPag As Ntz" +
                        " FROM " +
                        pLibDatos + ".PGDDIR " +
                        " WHERE " +
                        " PGDCMPEGR = " + pEgreso;
                }
                else if (pConcepto == "ANT")
                {
                    //Traer datos del anticipo y la legalizacion
                    tSql = "SELECT T3.ANTSEC as Num1, " +
                        " T1.OblNdc AS Num2," +
                        " T1.OblFch AS Fecha," +
                        " T1.OblVnt As Valor," +
                        " T3.AntNotVlr As Valor2," +
                        " T1.OblMon as Moneda," +
                        " T1.OblObl As Documento," +
                        " T1.OblObs As Observacion," +
                        " '0' As Ntz" +
                        " FROM " +
                        pLibDatos + ".OBLBAS As T1, " +
                        pLibDatos + ".ANTBAS As T2, " +
                        pLibDatos + ".ANTNOT As T3, " +
                        pLibDatos + ".DOCTIP As T4 " +
                        " WHERE " +
                        " T1.OBLOFI = T3.ANTOFI " +
                        " AND T1.OBLOBL = T3.ANTNOTOBL " +
                        " AND T1.OblTipDoc = T4.DOCTIP " +
                        " AND T2.ANTCMPEGR = " + pEgreso +
                        " AND T2.ANTOFI = T3.ANTOFI " +
                        " AND T2.ANTSEC = T3.ANTSEC " +
                        " UNION" +
                        " SELECT T3.ANTSEC as Num1, " +
                        " T1.OblNdcH AS Num2," +
                        " T1.OblFchH AS Fecha," +
                        " T1.OblVntH As Valor," +
                        " T3.AntNotVlr As Valor2," +
                        " T1.OblMonH as Moneda," +
                        " T1.OblOblH As Documento," +
                        " T1.OblObsH As Observacion," +
                        " '0' As Ntz" +
                        " FROM " +
                        pLibDatos + ".OBLBASH As T1, " +
                        pLibDatos + ".ANTBAS As T2, " +
                        pLibDatos + ".ANTNOT As T3, " +
                        pLibDatos + ".DOCTIP As T4 " +
                        " WHERE " +
                        " T1.OBLOFIH = T3.ANTOFI " +
                        " AND T1.OBLOBLH = T3.ANTNOTOBL " +
                        " AND T1.OblTipDocH = T4.DOCTIP " +
                        " AND T2.ANTCMPEGR = " + pEgreso +
                        " AND T2.ANTOFI = T3.ANTOFI " +
                        " AND T2.ANTSEC = T3.ANTSEC " +
                        " ORDER By Num2";
                }
                else
                {
                    //Traer datos de la factura
                    tSql = "SELECT T3.DocAbvTip as Num1, " +
                        " T1.OblNdc AS Num2," +
                        " T1.OblFch AS Fecha," +
                        " T1.OblVnt As Valor," +
                        " T2.PagVlr As Valor2," +
                        " T1.OblMon as Moneda," +
                        " T1.OblObl As Documento," +
                        " T1.OblObs As Observacion," +
                        " '0' As Ntz" +
                        " FROM " +
                        pLibDatos + ".OBLBAS As T1, " +
                        pLibDatos + ".PAGBAS As T2, " +
                        pLibDatos + ".DOCTIP As T3 " +
                        " WHERE " +
                        " T1.OBLOFI = T2.OBLOFI " +
                        " AND T1.OBLOBL = T2.OBLOBL " +
                        " AND T1.OblTipDoc = T3.DOCTIP " +
                        " AND T2.PAGSTS = '6'" +
                        " AND T2.PAGCMPEGR = " + pEgreso +
                        " UNION" +
                        " SELECT T3.DocAbvTip as Num1, " +
                        " T1.OblNdcH AS Num2," +
                        " T1.OblFchH AS Fecha," +
                        " T1.OblVntH As Valor," +
                        " T2.PagVlrH As Valor2," +
                        " T1.OblMonH as Moneda," +
                        " T1.OblOblH As Documento," +
                        " T1.OblObsH As Observacion," +
                        " '0' As Ntz" +
                        " FROM " +
                        pLibDatos + ".OBLBASH As T1, " +
                        pLibDatos + ".PAGBASH As T2, " +
                        pLibDatos + ".DOCTIP As T3 " +
                        " WHERE " +
                        " T1.OBLOFIH = T2.OBLOFIH " +
                        " AND T1.OBLOBLH = T2.OBLOBLH " +
                        " AND T1.OblTipDocH = T3.DOCTIP " +
                        " AND T2.PAGSTSH = '6'" +
                        " AND T2.PAGCMPEGRH = " + pEgreso +
                        " UNION" +
                        " SELECT T1.NcpCon as Num1, " +
                        " T1.NcpNdc AS Num2," +
                        " T1.NcpFch AS Fecha," +
                        " T1.NcpVlr As Valor," +
                        " T1.NcpVlr As Valor2," +
                        " T1.NcpMon as Moneda," +
                        " 'Nota' As Documento," +
                        " 'Nota' As Observacion," +
                        " NcpNtz As Ntz" +
                        " FROM " +
                        pLibDatos + ".NCPBAS As T1 " +
                        " WHERE " +
                        " T1.NcpSts = '7'" +
                        " AND T1.NcpCmpEgr = " + pEgreso +
                        " ORDER By Num1";
                }
                DataTable tTabla = p.leerDatosSQL(tSql);
                return tTabla;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
